# -*- coding: utf-8 -*-
# All Rights Reserved 
# @Time    : 2023/10/2520:28
# @File    : code_mysql.py
__author__ = 'Small Fu'

import pymysql


class open_mysql:
    def __init__(self):
        # 创建mysql链接
        conn = pymysql.connect(
            host='localhost', port=3306, user='root', password='123123', db='text_db'
        )
        # 获取游标
        cursor = conn.cursor()
        self.conn = conn
        self.cursor = cursor

    def get_conn(self):
        return self.conn
    def get_all(self, table='dzyx'):
        '''
        获取所有代码
        :param table: 处理的表名
        :return: {[item]}
        '''
        self.cursor.execute(f'''
        select * from {table};
        ''')
        result = self.cursor.fetchall()
        return result

    def get_num(self, q='', c=''):
        '''
        按照指定的查询条件来查询
        :param q: 查询条件
        :param c: 限制
        :return: 返回{[item]}
        '''
        self.cursor.execute(f'''
        select * from dzyx where {q}{c}
        ''')
        result = self.cursor.fetchall()
        return result

    def create_sql(self):
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS dzyx (
            Rank INT,
            Name VARCHAR(255),
            Platform VARCHAR(255),
            Year INT,
            Genre VARCHAR(255),
            Publisher VARCHAR(255),
            NA_Sales DECIMAL(10, 2),
            EU_Sales DECIMAL(10, 2),
            JP_Sales DECIMAL(10, 2),
            Other_Sales DECIMAL(10, 2),
            Global_Sales DECIMAL(10, 2)
        )
        """
        self.cursor.execute(create_table_sql)

    def pull_sql(self):
        import csv

        with open('data/电子游戏销量数据集.csv', 'r', encoding='utf-8') as csv_file:
            csv_reader = csv.reader(csv_file)
            next(csv_reader)  # 跳过CSV文件的标题行
            for row in csv_reader:
                insert_sql = """
                INSERT INTO dzyx (Rank, Name, Platform, Year, Genre, Publisher, NA_Sales, EU_Sales, JP_Sales, Other_Sales, Global_Sales)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                try:
                    self.cursor.execute(insert_sql, (
                        int(row[0]), row[1], row[2], int(row[3]), row[4], row[5], float(row[6]), float(row[7]),
                        float(row[8]),
                        float(row[9]), float(row[10])))
                    self.conn.commit()  # 提交事务

                except Exception as d:
                    pass

    def sql_close(self):
        self.cursor.close()
        self.conn.close()
if __name__ == '__main__':
    a = open_mysql()
    a.create_sql()
    a.pull_sql()
